package com.sqlcheck.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sqlcheck.common.enums.DatabaseType;
import com.sqlcheck.entity.LLMPromptTemplate;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Optional;

@Mapper
public interface LLMPromptTemplateRepository extends BaseMapper<LLMPromptTemplate> {

    /**
     * 根据模板键查询模板
     */
    @Select("SELECT * FROM llm_prompt_templates WHERE template_key = #{templateKey} AND enabled = 1 AND deleted = 0")
    Optional<LLMPromptTemplate> findByTemplateKeyAndEnabledTrue(@Param("templateKey") String templateKey);

    /**
     * 根据数据库类型查询最佳模板
     */
    @Select("SELECT * FROM llm_prompt_templates WHERE " +
            "(source_db_type = #{sourceDbType} OR source_db_type = 'ANY') AND " +
            "(target_db_type = #{targetDbType} OR target_db_type = 'ANY') AND " +
            "is_active = 1 AND deleted = 0 " +
            "ORDER BY priority DESC, usage_count DESC LIMIT 1")
    Optional<LLMPromptTemplate> findBestTemplate(@Param("sourceDbType") DatabaseType sourceDbType, 
                                                @Param("targetDbType") DatabaseType targetDbType);

    /**
     * 根据分类查询模板
     */
    @Select("SELECT * FROM llm_prompt_templates WHERE category = #{category} AND is_active = 1 AND deleted = 0 ORDER BY priority DESC")
    List<LLMPromptTemplate> findByCategory(@Param("category") String category);

    /**
     * 查询系统模板
     */
    @Select("SELECT * FROM llm_prompt_templates WHERE is_system = 1 AND is_active = 1 AND deleted = 0 ORDER BY priority DESC")
    List<LLMPromptTemplate> findSystemTemplates();

    /**
     * 更新模板使用次数
     */
    @Update("UPDATE llm_prompt_templates SET usage_count = usage_count + 1 WHERE id = #{templateId}")
    void incrementUsageCount(@Param("templateId") Long templateId);

    /**
     * 更新模板成功率
     */
    @Update("UPDATE llm_prompt_templates SET success_rate = #{successRate}, avg_processing_time = #{avgProcessingTime} WHERE id = #{templateId}")
    void updateStatistics(@Param("templateId") Long templateId, 
                         @Param("successRate") Double successRate, 
                         @Param("avgProcessingTime") Integer avgProcessingTime);
}